package io.zbus.data.impl.meta;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.support.JdbcUtils;

import io.zbus.data.impl.dialect.DialectFactory;
import io.zbus.data.kit.JsonKit;
 

public class MetaReader {
	private static final Logger logger = LoggerFactory.getLogger(MetaReader.class);
	
	private DataSource dataSource;
	
	public MetaReader(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	public MetaData reflect() {
		if(dataSource == null) {
			throw new IllegalStateException("Missing DataSource");
		}
		Connection conn = null;
		try {
			conn = dataSource.getConnection();  
			return MetaReader.reflect(conn);  
			
		} catch (SQLException e) {
			throw new IllegalStateException(e.getMessage(), e.getCause());
		} finally {
			JdbcUtils.closeConnection(conn);
		}  
	}
	
	
	
	public static MetaData reflect(Connection conn) throws SQLException {
		MetaData meta = new MetaData();
		Map<String, Table> tables = new HashMap<>();  
		meta.tables = tables;
		
		DatabaseMetaData metaDb = conn.getMetaData();  
		meta.productName = metaDb.getDatabaseProductName();
		meta.productVersion = metaDb.getDatabaseProductVersion();
		logger.debug("MetaData read begin: " + conn.getCatalog());
		try(ResultSet rsTable = metaDb.getTables(conn.getCatalog(), null, null, new String[]{"TABLE"})){
			while(rsTable.next()) { 
				Table table = new Table(); 
				//1) get table base info
				ResultSetMetaData metaTable = rsTable.getMetaData();
				for(int i=0; i<metaTable.getColumnCount(); i++) {
					String colName = metaTable.getColumnName(i+1);
					Object colValue = rsTable.getObject(i+1);
					assignCapKeyValue(table, colName, colValue); 
				} 
				tables.put(table.tableName, table);  
				 
					
				//2) get columns of current table
				try(ResultSet rsCol = metaDb.getColumns(table.tableCat, null, table.tableName, null)){
					ResultSetMetaData metaCol = rsCol.getMetaData();
					while(rsCol.next()) { 
						Column col = new Column();
						for(int i=0; i<metaCol.getColumnCount(); i++) { 
							String colName = metaCol.getColumnName(i+1);
							Object colValue = rsCol.getObject(i+1);  
							assignCapKeyValue(col, colName, colValue);  
						} 
						if(col.isAutoincrement != null && col.isAutoincrement) {
							table.autoColumn = col;
						}
						table.columnsInSeq.add(col);
						table.columns.put(col.columnName, col);  
					} 
				}
				
				//3) get primary keys 
				try(ResultSet rsPk = metaDb.getPrimaryKeys(table.tableCat, null, table.tableName)){
					ResultSetMetaData metaPk = rsPk.getMetaData(); 
					while(rsPk.next()) {
						PrimaryKey pk = new PrimaryKey();
						for(int i=0; i<metaPk.getColumnCount(); i++) {
							String colName = metaPk.getColumnName(i+1);
							Object colValue = rsPk.getObject(i+1); 
							assignCapKeyValue(pk, colName, colValue);  
						} 
						table.primaryKeys.put(pk.columnName, pk);
					}
				}
				logger.debug("MetaData Table: '" + table.tableName + "' OK");
			} 
		} 
		logger.debug("MetaData read end, total=" + meta.tables.size());
		
		meta.dialect = DialectFactory.fromProductName(meta.productName);
		return meta;
	}
	
	
	private static boolean assignCapKeyValue(Object obj, String capKey, Object value) {
    	if(obj == null) return false; 
    	String[] bb = capKey.split("[_]"); 
    	String res = "";
    	for(String b : bb) {
    		res += b.substring(0,1).toUpperCase() + b.substring(1).toLowerCase();
    	}
    	String methodName = "set"+res;
    	String fieldName = res.substring(0,1).toLowerCase() + res.substring(1);   
    	
    	try { 
			Field f = obj.getClass().getDeclaredField(fieldName);
			if(f != null) {
				value = JsonKit.convert(value, f.getType());
				f.set(obj, value);
				return true;
			}
		} catch (Exception e) { 
			//ignore 
		}    
    	
    	try {
			Method m = obj.getClass().getMethod(methodName, Object.class);
			if(m != null) {
				value = JsonKit.convert(value, m.getParameterTypes()[0]);
				m.invoke(obj, value);
				return true;
			}
			Field f = obj.getClass().getDeclaredField(fieldName);
			f.set(obj, value);
			return true;
		} catch (Exception e) { 
			//ignore 
		}  
    	return false;
    } 
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
}
